# __author__ = 'Hoyin'
# __date__ = '2020/06/02'
# __Desc__ = 从数据库中导出数据到excel数据表中

import os
import sys
import cx_Oracle
import pymysql
import time
from export.Logger import Logger

log = Logger('db.log', level='info').logger


class DB(object):

    def get_data(self, dbtype='test', sql='select now()'):
        try:
            if dbtype == 'test':
                log.info('连接测试库')
                conn = pymysql.connect(user='root', password='123456', host='localhost', port=3306)
            elif dbtype == 'paas':
                log.info('连接PaaS库')
                conn = pymysql.connect(user='query', password='Xdjk@query', host='10.80.16.66')
            elif dbtype == 'posp':
                log.info('连接收单库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'Xdjk_dba', '10.80.16.50/pospst', mode=cx_Oracle.SYSDBA)
            elif dbtype == 'his':
                log.info('连接历史库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'oracle', '10.80.16.37/his', mode=cx_Oracle.SYSDBA)
            elif dbtype == 'hlw':
                log.info('连接互联网库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'EJhvmykOSWkyNlHJ', '10.80.16.25/webpay', mode=cx_Oracle.SYSDBA)
            elif dbtype == 'risk':
                log.info('连接风控库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'l3jx6mcAFYakMEej', '10.80.16.29/riskctl', mode=cx_Oracle.SYSDBA)
            elif dbtype == 'jyx':
                log.info('连接JYX库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'oracleJYX1', '10.80.16.49/posp1', mode=cx_Oracle.SYSDBA)
            elif dbtype == 'wkkj':
                log.info('连接wkkj库')
                conn = pymysql.connect(user='root', password='h41vDb0fkh1g', host='10.80.16.33')
            elif dbtype == 'bi':
                log.info('连接bi库')
                conn = pymysql.connect(user='root', password='DCH8Tztd', host='10.80.16.61')
            elif dbtype == 'paas_new':
                log.info('连接new-paas库')
                conn = pymysql.connect(user='query', password='Xdjk@query', host='10.80.16.74')
            elif dbtype == 'business':
                log.info('连接business库')
                conn = pymysql.connect(user='business_data', password='2uAiKqU4', host='10.80.16.62', port=3307)

        except Exception as e:
            log.error("连接数据库失败！")

            return False, False

        start_time = time.time()
        
        try:
            cursor = conn.cursor()
            log.info("开始执行sql...")
            row_num = cursor.execute(sql)
        except Exception as e:
            log.error("执行sql失败！请检查sql语法问题！")
            log.error(e)
            return False, False

        data = []
        
        while True:
            try:
                row = cursor.fetchone()
            except Exception as e:
                pass
            if row is None:
                break
            data.append(row)

        # 获取表头
        fields = cursor.description
        end_time = time.time()

        if data and len(data) != 0:
            cursor.close()
            conn.close()
            log.info("执行时间：{}".format(end_time-start_time))
            return data, fields
        elif len(data) == 0:
            log.error("sql提取行数为0")
            return False, False


if __name__ == "__main__":
    results, fields = DB().get_data(0, "select now()")
    # print(results)
